package com.ohuang.simpledb;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

class SimpleDataBase {

    private DbHelper dbHelper;
    private Context context;
    private String dbName;

    public SimpleDataBase(Context context, String dbName) {
        this.context = context;
        this.dbName = dbName;
        dbHelper = new DbHelper(context, dbName);
    }

    public void add(SimpleData simpleData) {
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("data", simpleData.data);
        values.put("key", simpleData.key);
        dbDatabase.insert(DbHelper.tableName, null, values);
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
    }

    public void delete(int id) {
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        dbDatabase.delete(DbHelper.tableName, "id= ? ",
                new String[]{String.valueOf(id)});
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
    }


    public void update(SimpleData simpleData, int id) {
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("data", simpleData.data);
        values.put("key", simpleData.key);
        dbDatabase.update(DbHelper.tableName, values, "id=?",
                new String[]{String.valueOf(id)});
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
    }

    public List<SimpleData> findSimpleData(int id) {
        String stringId = id + "";
        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                "id=?", new String[]{stringId}, null, null,
                null);
        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }

    public List<SimpleData> findSimpleDataByKey(String key) {

        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                "key=?", new String[]{key}, null, null,
                null);
        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }

    public List<SimpleData> find(int pageSize, int pageNumber) {
        if (pageSize < 1) {
            pageSize = 10;// 每页10条数据
        }
        if (pageNumber < 1) {
            pageNumber = 1; // 第一页
        }
// 计算偏移量
        int offset = (pageNumber - 1) * pageSize;
        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                null, null, null, null,
                "id asc", offset + "," + pageSize);

        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }

    public List<SimpleData> findSortByKey(int pageSize, int pageNumber) {
        if (pageSize < 1) {
            pageSize = 10;// 每页10条数据
        }
        if (pageNumber < 1) {
            pageNumber = 1; // 第一页
        }
// 计算偏移量
        int offset = (pageNumber - 1) * pageSize;
        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                null, null, null, null,
                "key asc", offset + "," + pageSize);

        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }

    public List<SimpleData> findForDESC(int pageSize, int pageNumber) {
        if (pageSize < 1) {
            pageSize = 10;// 每页10条数据
        }
        if (pageNumber < 1) {
            pageNumber = 1; // 第一页
        }

// 计算偏移量
        int offset = (pageNumber - 1) * pageSize;
        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                null, null, null, null,
                "id desc", offset + "," + pageSize);

        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }

    public List<SimpleData> findSortByKeyForDESC(int pageSize, int pageNumber) {
        if (pageSize < 1) {
            pageSize = 10;// 每页10条数据
        }
        if (pageNumber < 1) {
            pageNumber = 1; // 第一页
        }

// 计算偏移量
        int offset = (pageNumber - 1) * pageSize;
        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                null, null, null, null,
                "key desc", offset + "," + pageSize);

        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }


    public List<SimpleData> findAll() {
        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                null, null, null, null,
                "id asc");

        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }

    public List<SimpleData> findAllSortByKey() {
        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                null, null, null, null,
                "key asc");

        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }

    /**
     * 降序排列
     *
     * @return
     */
    public List<SimpleData> findAllForDESC() {
        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                null, null, null, null,
                "id desc");

        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }

    public List<SimpleData> findAllSortByKeyForDESC() {
        List<SimpleData> list = new ArrayList<>();
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor = dbDatabase.query(DbHelper.tableName, new String[]{"id", "data", "key"},
                null, null, null, null,
                "key desc");

        while (cursor.moveToNext()) {
            SimpleData data = new SimpleData();
            data.id = cursor.getInt(0);
            data.data = cursor.getString(1);
            data.key = cursor.getString(2);
            list.add(data);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return list;
    }

    public int  getDataSize() {
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        Cursor cursor  =dbDatabase.rawQuery("SELECT COUNT(*) FROM "+DbHelper.tableName,null);
        int size=0;
        if (cursor.moveToNext()){
            size=cursor.getInt(0);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }
        return size;
    }


    public void deleteAll() {
        SQLiteDatabase dbDatabase = dbHelper.getWritableDatabase();
        dbDatabase.execSQL("DELETE FROM " + DbHelper.tableName);
        if (dbDatabase.isOpen()) {
            dbDatabase.close();
        }

    }

}
